<?php
	include_once('dbcon.php');
	include_once('constants.php');
	include_once('error_log_db.php');
	include_once('update_user_points_db.php');

	function changeAnonymousState($content_id, $user_id) {
		global $mysqli;

		if (isUserValid($user_id) == false) {
			logMsg($user_id, "CHANGE ANONYMOUS STATE INVALID. content_id=".$content_id);
			return;
		}

		$mysqli->query("UPDATE contents SET isAnonymous=1-isAnonymous WHERE content_id=".$content_id);
		logIfError($mysqli, $user_id, "general_functions_db", "changeAnonymousState", "1");
	}

	function removeContent($content_id, $user_id) {
		global $mysqli;

		$content_query = $mysqli->query("SELECT * FROM contents WHERE content_id='".$content_id."'");
		logIfError($mysqli, $user_id, "general_functions_db", "removeContent", "1");
		$content = $content_query->fetch_assoc();

		if (time() - strtotime($content['creation_date']) > CONTENT_MAX_REMOVE_TIME + SERVER_SIDE_EXTRA_WAIT_TIME) {
			logMsg($user_id, "REMOVE CONTENT INVALID TIME. content_id=".$content_id.". Content=".$content['content']);
			return;
		}

		if ($content['user_id'] != $user_id) {
			logMsg($user_id, "REMOVE CONTENT INVALID USER. content_id=".$content_id.". Owner_user=".$content['user_id']." Fake_user=".$user_id);
			return;
		}

		logMsg($user_id, "Remove content. content_id=".$content_id.". Content=".$content['content']);

		$mysqli->autocommit(false);
		$mysqli->query("DELETE FROM contents WHERE content_id=".$content_id);
		$mysqli->query("DELETE FROM contents_califications WHERE content_id=".$content_id);
		$mysqli->commit();
		$mysqli->autocommit(true);

		logIfError($mysqli, $user_id, "general_functions_db", "removeContent", "2");

		updateUserPoints($content['user_id']);
	}

	function removeCategory($category_id, $user_id) {
		global $mysqli;
		
		$category_query = $mysqli->query("SELECT * FROM categories WHERE category_id='".$category_id."'");
		$category = $category_query->fetch_assoc();
		logIfError($mysqli, "general_functions_db", "removeCategory", "1");

		if (time() - strtotime($category['creation_date']) > CATEGORY_MAX_REMOVE_TIME + SERVER_SIDE_EXTRA_WAIT_TIME) {
			logMsg($user_id, "REMOVE CATEGORY INVALID TIME. category_id=".$category_id.". Name=".$category['name']);
			return;
		}

		if ($category['user_id'] != $user_id) {
			logMsg($user_id, "REMOVE CATEGORY INVALID USER. category_id=".$category_id.". Owner_user=".$content['user_id']." Fake_user=".$user_id);
			return;
		}

		logMsg($user_id, "Remove category. category_id=".$category_id.". Name=".$category['name']);

		$users_query = $mysqli->query("SELECT user_id FROM contents WHERE content_id='".$category_id."'");
		logIfError($mysqli, $user_id, "general_functions_db", "removeCategory", "2");

		$mysqli->autocommit(FALSE);
		$mysqli->query("DELETE FROM contents_califications WHERE content_id IN ( SELECT content_id FROM contents WHERE category_id=".$category_id.")");
		$mysqli->query("DELETE FROM contents WHERE category_id=".$category_id);
		$mysqli->query("DELETE FROM categories WHERE category_id=".$category_id);
		$mysqli->commit();
		$mysqli->autocommit(TRUE);

		logIfError($mysqli, $user_id, "general_functions_db", "removeCategory", "3");


		while ($row = $category_query->fetch_assoc()) {
			updateUserPoints($row['user_id']);
		}
	}

	function buildContentArray($content_request, $user_id) {
		global $mysqli;

		$calification_query = null;

		if ($user_id != -1 && $user_id != 0) {
			$calification_query = $mysqli->query("SELECT calification FROM contents_califications WHERE content_id=".$content_request['content_id']." && user_id=".$user_id." LIMIT 1");
			logIfError($mysqli, $user_id, "general_functions_db", "buildContentArray", "1");
		}

		if ($calification_query != null) {
			if ($calification_query->num_rows == 0) {
				$calification = 0;
			} else {
				$content_calification = $calification_query->fetch_assoc();
				
				$calification = $content_calification['calification'];
			}
		}

		$total_likes_dislikes = $content_request['likes'] + $content_request['dislikes'];
		if ($content_request['likes'] != 0 || $content_request['dislikes'] != 0)
			$fraction = $content_request['likes'] / $total_likes_dislikes;
		else
			$fraction = 0;
		
		$content = array('content_id' => $content_request['content_id'],
						'user_id' => $content_request['user_id'],
						'first_name' => $content_request['first_name'],
						'last_name' => $content_request['last_name'],
						'content' => addslashes($content_request['content']),
						'likes' => $content_request['likes'],
						'dislikes' => $content_request['dislikes'],
						'creation_date' => $content_request['creation_date'],
						'is_anonymous' => $content_request['isAnonymous'], 
						'category_id' => $content_request['category_id'], 
						'fraction_likes' => round($fraction, PERCENTAGE_DECIMALS),
						'points' => round($content_request['points'], POINTS_DECIMALS),
						'rank' => get_content_rank($content_request['content_id'], $user_id));
		
		if (isset($content_request['category_name']))
			$content['category_name'] = $content_request['category_name'];
		
		if ($calification_query != null)
			$content['calification'] = $calification;

		if ($content_request['isAnonymous'] == "1") {
			$content['first_name'] = "";
			$content['last_name'] = "";
		}

		return $content;
	}

	function buildCategoryArray($category_request, $user_id) {
		global $mysqli;

		$total_likes_dislikes = $category_request['likes'] + $category_request['dislikes'];
		if ($category_request['likes'] != 0 || $category_request['dislikes'] != 0)
			$fraction = $category_request['likes'] / $total_likes_dislikes;
		else
			$fraction = 0;

		$category = array('category_id' => $category_request['category_id'],
						'name' => addslashes($category_request['name']),
						'user_id' => $category_request['user_id'],
						'likes' => $category_request['likes'],
						'dislikes' => $category_request['dislikes'],
						'score' => $category_request['score'],
						'fraction_likes' => round($fraction, PERCENTAGE_DECIMALS),
						'score' => $category_request['score'],
						'creation_date' => $category_request['creation_date'],
						'rank' => get_category_rank($category_request['category_id'], $user_id));
		

		return $category;
	}

	function get_category_rank($category_id, $user_id) {
		global $mysqli;
		
		$mysqli->query("SET @rank = 0");
		$category_query = $mysqli->query("SELECT T.rank, T.score FROM ( SELECT @rank:=@rank+1 AS rank, score, category_id FROM categories ORDER BY score DESC, category_id ) AS T WHERE T.category_id=".$category_id);
		logIfError($mysqli, $user_id, "general_functions_db", "get_category_rank", "1");
		
		return $category_query->fetch_assoc()['rank'];
	}

	function get_content_rank($content_id, $user_id) {
		global $mysqli;
		
		$mysqli->query("SET @rank = 0");
		$content_query = $mysqli->query("SELECT T.rank, T.points FROM ( SELECT @rank:=@rank+1 AS rank, points, content_id FROM contents ORDER BY points DESC, content_id ) AS T WHERE T.content_id=".$content_id);
		logIfError($mysqli, $user_id, "general_functions_db", "get_content_rank", "1");
		
		return $content_query->fetch_assoc()['rank'];
	}

	function get_user_rank($user_id) {
		global $mysqli;
		
		if ($user_id == 0) {
			return "-1";
		}

		$mysqli->query("SET @rank = 0");
		$user_query = $mysqli->query("SELECT T.rank, T.points FROM ( SELECT @rank:=@rank+1 AS rank, points, user_id FROM users ORDER BY points DESC, user_id ) AS T WHERE T.user_id=".$user_id);
		logIfError($mysqli, $user_id, "general_functions_db", "get_user_rank", "1");
		
		return $user_query->fetch_assoc()['rank'];
	}

	function isUserValid($user_id) {
		global $mysqli;

		$user_query = $mysqli->query("SELECT 'Exists' FROM users WHERE user_id='".$user_id."' LIMIT 1");

		return ($user_query->num_rows > 0);
	}

	function adaptToSql($string) {
		global $mysqli;

		return $mysqli->real_escape_string($string);
	}
?>